package top.fyxxxx.dbdesign.dao;

import top.fyxxxx.dbdesign.data.*;

import java.sql.*;
import java.util.*;

public class DBoperate {
    private static DBoperate dBoperate=new DBoperate();
    private DBoperate(){}
    public static DBoperate getInstance(){return dBoperate;}
    public int init()
    {
        return Connect.init();
    }


    public int insertAccount(Account account)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "insert into user (account,password,uname,uid,rdate,power) values(?,?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1,account.getAccount());
            pstmt.setString(2,account.getPassword());
            pstmt.setString(3,account.getName());
            pstmt.setLong(4,account.getUid());
            pstmt.setTimestamp(5,new Timestamp(account.getCreateTime()));
            pstmt.setLong(6,account.getPower());
            i = pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int insertSignin(SignInRecord signInRecord)//用户uid在cid课程的sid任务中签到
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "insert into signIn (uid,cid,sid,sidate) values(?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, signInRecord.getBelongToUid());
            pstmt.setLong(2, signInRecord.getBelongToCid());
            pstmt.setLong(3, signInRecord.getBelongToSid());
            pstmt.setTimestamp(4, new Timestamp(signInRecord.getSdate()));
            i = pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int joinCourse(PersonalData personalData)
    {

        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "insert into uc (uid,cid,rdate,power) values(?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, personalData.getAccount().getUid());
            pstmt.setLong(2, personalData.getCourse().getCid());
            pstmt.setTimestamp(3,new Timestamp(personalData.getJoinTime()));
            pstmt.setLong(4,personalData.getPower());
            i = pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int exitCourse(long uid,long cid)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "delete from uc where uid=? and cid=?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1,uid);
            pstmt.setLong(2,cid);
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int updateJoinCourse(PersonalData personalData)
    {

        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "update uc set rdate=?,power=? where uid=? and cid=? ";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setTimestamp(1,new Timestamp(personalData.getJoinTime()));
            pstmt.setLong(2,personalData.getPower());
            pstmt.setLong(3, personalData.getAccount().getUid());
            pstmt.setLong(4, personalData.getCourse().getCid());
            i = pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int insertCourse(Course course)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "insert into Course (cname,cid,rdate,uid) values(?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1,course.getName());
            pstmt.setLong(2,course.getCid());
            pstmt.setTimestamp(3,new Timestamp(course.getCreateTime()));
            pstmt.setLong(4,course.getCreateByUid());
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int insertSignInTask(SignInTask signInTask)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "insert into signInTask (uid,cid,sid,rdate,beginTime,endTime) values(?,?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1,signInTask.getCreateByUid());
            pstmt.setLong(2,signInTask.getBelongToCid());
            pstmt.setLong(3,signInTask.getSid());
            pstmt.setTimestamp(4,new Timestamp(signInTask.getCreateTime()));
            pstmt.setTimestamp(5,new Timestamp(signInTask.getBeginTime()));
            pstmt.setTimestamp(6,new Timestamp(signInTask.getEndTime()));

            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }

    public int updateAccount(Account account)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "update user set account=?,password=?,uname=?,rdate=?,power=? where uid=?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1,account.getAccount());
            pstmt.setString(2,account.getPassword());
            pstmt.setString(3,account.getName());
            pstmt.setTimestamp(4,new Timestamp(account.getCreateTime()));
            pstmt.setLong(5,account.getPower());
            pstmt.setLong(6,account.getUid());
            i = pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int updateCourse(Course course)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "update Course set cname=?,rdate=?,uid=? where cid=?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1,course.getName());
            pstmt.setTimestamp(2,new Timestamp(course.getCreateTime()));
            pstmt.setLong(3,course.getCreateByUid());
            pstmt.setLong(4,course.getCid());
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }
    public int updateSignInTask(SignInTask signInTask)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "update signInTask set uid=?,cid=?,rdate=?,beginTime=?,endTime=? where sid=?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1,signInTask.getCreateByUid());
            pstmt.setLong(2,signInTask.getBelongToCid());
            pstmt.setTimestamp(3,new Timestamp(signInTask.getCreateTime()));
            pstmt.setTimestamp(4,new Timestamp(signInTask.getBeginTime()));
            pstmt.setTimestamp(5,new Timestamp(signInTask.getEndTime()));
            pstmt.setLong(6,signInTask.getSid());

            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }

    public int deleteCourse(long cid)
    {
        Connection conn = Connect.getConn();
        int i = 0;

        String[] sql ={"delete from course where cid=?", "delete from uc where cid=?", "delete from  signInTask  where cid=?", "delete from signIn where cid=?"};



        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

        PreparedStatement pstmt;
        try {
            for(int ii=0;ii<4;ii++)
            {
                pstmt = (PreparedStatement) conn.prepareStatement(sql[ii]);
                pstmt.setLong(1,cid);
                i = pstmt.executeUpdate();
                pstmt.close();
            }
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        return i;
    }
    public int deleteSignInTask(long sid)
    {
        Connection conn = Connect.getConn();
        int i = 0;

        String[] sql ={"delete from signInTask where sid=?", "delete from signIn where sid=?"};
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
        PreparedStatement pstmt;
        try {
            for(int ii=0;ii<2;ii++)
            {
                pstmt = (PreparedStatement) conn.prepareStatement(sql[ii]);
                pstmt.setLong(1,sid);
                i = pstmt.executeUpdate();
                pstmt.close();
            }
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        return i;
    }
    public int deleteSignIn(long uid,long sid)
    {
        Connection conn = Connect.getConn();
        int i = 0;
        String sql = "delete from SignIn where uid=? and sid=?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1,uid);
            pstmt.setLong(2,sid);
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return i;
    }


    public PersonalData getPersonalData(long uid,long cid)
    {
        Account account=getAccountByUid(uid);//用户的账号信息
        Course course=getCourse(cid);//课程信息
        TreeMap<SignInTask,SignInRecord> totalTask=new TreeMap<>();
        List<Long>tasks= getTaskList(cid);
        long joinTime=0;
        long power=0;
        for(long sid : tasks)
        {
            SignInTask signInTask=getSignInTask(sid);
            SignInRecord signInRecord=getSignInRecord(uid,sid);
            if(signInRecord.getSdate()==0)signInRecord=null;
            totalTask.put(signInTask,signInRecord);
        }

        Connection conn = Connect.getConn();
        String sql = "select rdate,power from uc where uid = ? and cid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            pstmt.setLong(2, cid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                joinTime = rs.getTimestamp("rdate").getTime();
                power = rs.getLong("power");
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new PersonalData(account,course,totalTask,joinTime,power);

    }
    public TaskData getTaskData(long sid)
    {
        SignInTask task=null;
        List<SignInRecord> records=null;
        task=getSignInTask(sid);
        records=getSignInHistoryBySid(sid);
        return new TaskData(task,records);
    }
    public Account getAccountByUid(long uid)
    {
        String account=null;//账号
        String password=null;//密码
        String name=null;//姓名
        long createTime=0;//创建时间
        long power=0;//权限（是否可以创建课程）
        Set<Long>joinCids=new HashSet<>();//加入的课程
        Connection conn = Connect.getConn();


        String sql = "select * from user where uid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                account = rs.getString("account");
                password = rs.getString("password");
                name = rs.getString("uname");
                createTime = rs.getTimestamp("rdate").getTime();
                power = rs.getLong("power");
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        sql="select cid from uc where uid = ?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                joinCids.add(rs.getLong("cid"));
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Account(account,password,name,uid,createTime,power,joinCids);
    }
    public Account getAccountByaccount(String account)
    {
        String password=null;//密码
        String name=null;//姓名
        long uid=-1;//用户id
        long createTime=0;//创建时间
        long power=0;//权限（是否可以创建课程）
        Set<Long>joinCids=new HashSet<>();//加入的课程
        Connection conn = Connect.getConn();


        String sql = "select * from user where account = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, account);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = rs.getString("password");
                name = rs.getString("uname");
                uid = rs.getLong("uid");
                createTime = rs.getTimestamp("rdate").getTime();
                power = rs.getLong("power");
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        sql="select cid from uc where uid = ?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                joinCids.add(rs.getLong("cid"));
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Account(account,password,name,uid,createTime,power,joinCids);
    }

    public Course getCourse(long cid)
    {
        String name=null;//课程名
        long createTime=0;//创建课程时间
        long createByUid=0;//由Uid用户创建
        List<Long>taskList= getTaskList(cid);//签到任务编号列表
        Set<Long>powerUid=getPowerUid(cid);//有权限在此课程中创建签到任务的Uid
        Set<Long>memberUid=getMemberUid(cid);//加入此课程的Uid
        Connection conn = Connect.getConn();

        String sql = "select * from course where cid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, cid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                name = rs.getString("cname");
                createTime = rs.getTimestamp("rdate").getTime();
                createByUid = rs.getLong("uid");
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Course(name,cid,createTime,createByUid,taskList,powerUid,memberUid);
    }
    public List<Long>searchCourse(String re)
    {
        List<Long> cids=new LinkedList<>();
        Connection conn = Connect.getConn();

        String sql = "select cid from course where cname REGEXP ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, re);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                cids.add(rs.getLong("cid"));
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cids;
    }
    public List<Long>searchSignInTask(long nowDate,long cid)
    {
        List<Long> sids=new LinkedList<>();
        Connection conn = Connect.getConn();
        String sql = "select sid from signInTask where cid = ? and endTime > ? and beginTime < ? ";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, cid);
            pstmt.setTimestamp(2, new Timestamp(nowDate));
            pstmt.setTimestamp(3, new Timestamp(nowDate));
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                sids.add(rs.getLong("sid"));
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sids;
    }
    public SignInTask getSignInTask(long sid)
    {
        long createByUid=0;//由Uid用户建立
        long createTime=0;//创建时间
        long beginTime=0;//开始时间
        long endTime=0;//结束时间
        long belongToCid=0;//属于Cid课程
        Connection conn = Connect.getConn();

        String sql = "select * from signInTask where sid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, sid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                createByUid=rs.getLong("uid");
                createTime=rs.getTimestamp("rdate").getTime();
                beginTime=rs.getTimestamp("beginTime").getTime();
                endTime=rs.getTimestamp("endTime").getTime();
                belongToCid=rs.getLong("cid");
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new SignInTask(sid,createByUid,createTime,beginTime,endTime,belongToCid);
    }

    public SignInRecord getSignInRecord(long uid,long sid)
    {
        long belongToCid=0;//属于cid课程
        long sdate=0;//签到时间
        Connection conn = Connect.getConn();
        PreparedStatement pstmt;
        String sql = "select * from signIn where uid = ? and sid =?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            pstmt.setLong(2, sid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                belongToCid=rs.getLong("cid");
                sdate=rs.getTimestamp("sidate").getTime();
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new SignInRecord(uid,belongToCid,sid,sdate);
    }
    public Set<Long> getJoinCids(long uid)//获取此用户加入的课程的cid列表
    {
        Set<Long> joinCids=new HashSet<>();

        Connection conn = Connect.getConn();

        String sql = "select cid from uc where uid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                joinCids.add(rs.getLong("cid"));
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return joinCids;
    }

    public List<Long> getTaskList(long cid)//获取此课程的签到任务编号列表
    {
        List<Long> Sids=new LinkedList<>();

        Connection conn = Connect.getConn();

        String sql = "select sid from signInTask where cid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, cid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Sids.add(rs.getLong("sid"));
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Sids;
    }
    public Set<Long>getPowerUid(long cid)//有权限在此课程中创建签到任务的Uid
    {
        Set<Long>powerUid=new HashSet<>();
        Connection conn = Connect.getConn();

        PreparedStatement pstmt;
        String sql = "select uid,power from uc where cid = ?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, cid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                long uid=rs.getLong("uid");
                long power=rs.getLong("power");
                if(power>0)powerUid.add(uid);
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return powerUid;
    }
    public Set<Long>getMemberUid(long cid)//加入此课程的Uid
    {
        Set<Long>memberUid=new HashSet<>();
        Connection conn = Connect.getConn();

        PreparedStatement pstmt;
        String sql = "select uid from uc where cid = ?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, cid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                long uid=rs.getLong("uid");
                memberUid.add(uid);
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return memberUid;
    }
    public List<SignInRecord> getSignInHistoryByUid(long uid)//uid用户的签到记录
    {
        List<SignInRecord> signInRecords=new LinkedList<>();
        Connection conn = Connect.getConn();

        PreparedStatement pstmt;
        String sql = "select * from signIn where uid = ?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, uid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                SignInRecord signInRecord=new SignInRecord();
                signInRecord.setBelongToUid(uid);
                signInRecord.setBelongToCid(rs.getLong("cid"));
                signInRecord.setBelongToSid(rs.getLong("sid"));
                signInRecord.setSdate(rs.getTimestamp("sidate").getTime());
                signInRecords.add(signInRecord);
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return signInRecords;
    }
    public List<SignInRecord> getSignInHistoryBySid(long sid)//sid任务的签到记录
    {
        List<SignInRecord> signInRecords=new LinkedList<>();
        Connection conn = Connect.getConn();

        PreparedStatement pstmt;
        String sql = "select * from signIn where sid = ?";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setLong(1, sid);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                SignInRecord signInRecord=new SignInRecord();
                signInRecord.setBelongToUid(rs.getLong("uid"));
                signInRecord.setBelongToCid(rs.getLong("cid"));
                signInRecord.setBelongToSid(sid);
                signInRecord.setSdate(rs.getTimestamp("sidate").getTime());
                signInRecords.add(signInRecord);
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return signInRecords;
    }

}
